﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using S.ReportForms.Domain.Dto;
using S.ReportForms.Infrastructure.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace S.ReportForms.Infrastructure.Impl
{
    public class SheepStoredInRealTimeRepository:ISheepStoredInRealTimeRepository
    {
        private readonly IConfiguration configuration;

        public SheepStoredInRealTimeRepository(IConfiguration configuration)
        {
            this.configuration = configuration;
        }
        /// <summary>
        /// 羊场总存栏
        /// </summary>
        /// <param name="BreedType"></param>
        /// <returns></returns>
        public async Task<SheepStoredInRealTimeDto> GetSheepStoredInRealTimeDto(int BreedType)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT   count(DISTINCT EarTag) as StoredInRealStore FROM SheepFileModel WHERE  PresenceState=2  ");
                    if (BreedType != 0)
                    {
                        sqlQuery.Append("and FileType=@BreedType");
                    }
                    var result = connection.QueryFirstAsync<SheepStoredInRealTimeDto>(sqlQuery.ToString(), new { BreedType });
                    return await result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 种公详情
        /// </summary>
        /// <returns></returns>

        public async Task<List<BreedFatherDetailDto>> GetBreedFatherDetail()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT Variety,  count(DISTINCT EarTag) as StoredInRealStore FROM SheepFileModel WHERE  PresenceState=2 and  FileType=1 GROUP BY Variety");

                    var result = await connection.QueryAsync<BreedFatherDetailDto>(sqlQuery.ToString());
                    return result.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 后裔详情
        /// </summary>
        /// <returns></returns>
        public async Task<List<BreedChildDto>> GetBreedChild()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"		SELECT 
    GrowthStage,
    COUNT(*) AS Count
FROM 
    (
        SELECT 
            DATEDIFF(MONTH, sf.BirthDate, GETDATE()) + CASE WHEN DATEPART(DAY, sf.BirthDate) > DATEPART(DAY, GETDATE()) THEN -1 ELSE 0 END AS MonthAge, 
            DATEDIFF(DAY, sf.BirthDate, GETDATE()) AS Dayage, 
            CASE WHEN 
                DATEDIFF(MONTH, sf.BirthDate, GETDATE()) + CASE WHEN DATEPART(DAY, sf.BirthDate) > DATEPART(DAY, GETDATE()) THEN -1 ELSE 0 END >= 0 
                AND DATEDIFF(MONTH, sf.BirthDate, GETDATE()) + CASE WHEN DATEPART(DAY, sf.BirthDate) > DATEPART(DAY, GETDATE()) THEN -1 ELSE 0 END <= 3 
            THEN 1 
            WHEN 
                DATEDIFF(MONTH, sf.BirthDate, GETDATE()) + CASE WHEN DATEPART(DAY, sf.BirthDate) > DATEPART(DAY, GETDATE()) THEN -1 ELSE 0 END > 3 
                AND DATEDIFF(MONTH, sf.BirthDate, GETDATE()) + CASE WHEN DATEPART(DAY, sf.BirthDate) > DATEPART(DAY, GETDATE()) THEN -1 ELSE 0 END <= 6 
            THEN 2 
            ELSE 3 
            END AS GrowthStage,
            sf.EarTag
        FROM 
            SheepFileModel sf    WHERE sf.FileType=3 and PresenceState=2
    ) AS subquery  
GROUP BY
    GrowthStage;");

                    var result = await connection.QueryAsync<BreedChildDto>(sqlQuery.ToString());
                    return result.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 获取种母
        /// </summary>
        /// <returns></returns>
        public async Task<List<BreedMotherDto>> GetBreedMother()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"SELECT ReproductiveStateGroup,COUNT(*) AS TotalCount
                                        FROM 
                                            (SELECT 
                                                    CASE 
                                                        WHEN ReproductiveState IN (0, 1, 2, 3, 4, 5, 6) THEN '空怀'
                                                        WHEN ReproductiveState = 7 THEN '妊娠'
                                                        WHEN ReproductiveState = 8 THEN '哺乳'
                                                    END AS ReproductiveStateGroup
                                                FROM 
                                                    SheepFileModel
                                                WHERE 
                                                    FileType = 2 AND PresenceState = 2
                                            ) AS subquery
                                        GROUP BY 
                                        ReproductiveStateGroup");

                    var result = await connection.QueryAsync<BreedMotherDto>(sqlQuery.ToString());
                    StringBuilder sqlQueryEmpty = new StringBuilder();
                    //空怀详情
                    sqlQueryEmpty.Append($@"SELECT  ReproductiveState, COUNT(*) AS EmptyCount FROM  SheepFileModel WHERE  FileType = 2 AND PresenceState = 2 AND ReproductiveState NOT IN ( 7, 8) GROUP BY ReproductiveState");
                    result.ToList()[0].emptyHeartDtos = (await connection.QueryAsync<EmptyHeartDto>(sqlQueryEmpty.ToString())).ToList();
                    //妊娠详情
                    StringBuilder sqlQueryPregency = new StringBuilder();
                    //空怀详情
                    sqlQueryPregency.Append($@"		SELECT 
                          CASE
                            WHEN DATEDIFF(DAY, ReproductiveStateDate, GETDATE()) <= 100 THEN '妊娠前期'
                            WHEN DATEDIFF(DAY, ReproductiveStateDate, GETDATE()) <= 147 THEN '妊娠后期'
                            ELSE '超预产期'
                          END AS PregencyState,
                          COUNT(*) AS PregencyTotal
                        FROM 
                          SheepFileModel  WHERE ReproductiveState=7 and  PresenceState=2 AND FileType=2 
                   
                        GROUP BY 
                          CASE
                             WHEN DATEDIFF(DAY, ReproductiveStateDate, GETDATE()) <= 100 THEN '妊娠前期'
                            WHEN DATEDIFF(DAY, ReproductiveStateDate, GETDATE()) <= 147 THEN '妊娠后期'
                            ELSE '超预产期'
                          END");
                   if(result.Count() > 1)
                    {
                        result.ToList()[1].pregnancyTestDtos = (await connection.QueryAsync<PregnancyTestDto>(sqlQueryPregency.ToString())).ToList();
                    }
                    return result.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 获取种母详情
        /// </summary>
        /// <returns></returns>
        public async Task<List<MothersDetailDto>> GetMotherDto()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"		select sfarm.SheepFarmsFullName,sf.EarTag,sf.ReproductiveState,sf.ReproductiveStateDate,cm.BuildingName,fm.ColumnName,DATEDIFF(DAY, sf.ReproductiveStateDate, GETDATE()) DiffDays,sr.SurrenderDate from SheepFileModel  sf
                    left join SheepRescission sr on sf.EarTag =sr.EarTag  
                    LEFT JOIN ColumnManagementModel cm on cm.BuildingManagementId=sr.ShiftToBuildingManagementId
                    left JOIN FieldModel fm on fm.ColumnManagementId=sr.ShiftToColumnManagementId 
                    LEFT JOIN SheepFarmsModel sfarm on cm.SheepFarmCode=sfarm.SheepFarmsId 
                    WHERE PresenceState=2 AND FileType=2   
                    ");

                    var result = await connection.QueryAsync<MothersDetailDto>(sqlQuery.ToString());
                    result=result.GroupBy(m=>m.EarTag).Select(g=>g.OrderByDescending(m=>m.SurrenderDate).First()).ToList();
                    return result.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 获取种公详情
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>

        public async Task<List<FathersDetailDto>> GetFathersDto()
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();
                    sqlQuery.Append($@"select sf.EarTag,sf.Variety,cm.BuildingName,fm.ColumnName,sfarm.SheepFarmsFullName,sr.SurrenderDate from SheepFileModel  sf
                        left join SheepRescission sr on sf.EarTag =sr.EarTag  
                        LEFT JOIN ColumnManagementModel cm on cm.BuildingManagementId=sr.ShiftToBuildingManagementId
                        left JOIN FieldModel fm on fm.ColumnManagementId=sr.ShiftToColumnManagementId 
                        LEFT JOIN SheepFarmsModel sfarm on cm.SheepFarmCode=sfarm.SheepFarmsId
                        WHERE PresenceState=2 AND FileType=1");

                    var result = await connection.QueryAsync<FathersDetailDto>(sqlQuery.ToString());
                    result = result.GroupBy(m => m.EarTag).Select(g => g.OrderByDescending(m => m.SurrenderDate).First()).ToList();
                    return result.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
